W1: Database Concepts, DESCRIBE, SELECT, WHERE

Welcome!

Please sign-up for an account at Posit Cloud and accept our classroom invitation here: https://posit.cloud/spaces/689711/join?access_code=8kse5IYlL4kHIqZvKaQ6mXp8IMibFayMa10I8Izn

Our course website: https://intro-sql-fh.netlify.app/

Introductions

• Who am I?

Goals of the course

Culture of the course

Culture of the course

• Challenge: We sometimes struggle with our data science problems in isolation, unaware that other folks are working on similar things.

Format of the course

Badge of completion

We offer a badge of completion when you finish the course!

What it is:

• A display of what you accomplished in the course, shareable in your professional networks such as LinkedIn, similar to online education services such as Coursera.

What it isn’t:

• Accreditation through an university or degree-granting program.

Databases…

• What are some Databases you are interested in?

Database Management System (DBMS) consists of

• A user interface - how users interact with the database. In this class, our main way of interacting with databases is SQL (Structured Query Language).

• An execution engine - a software system that queries the data in storage. These can live on our machine, on a server within our network, or a server on the cloud.

• Data Storage - the physical location where the data is stored.

DBMS examples

Our underlying data model

Relational Database: Data is organized into multiple tables. Tables are connected via columns that share the same elements across tables.

A short survey on your interest and background

https://forms.gle/YADmDmukRKmGk2KFA

Let’s get started: connecting to the database

library(DBI)

con <- DBI::dbConnect(duckdb::duckdb(), "../data/GiBleed_5.3_1.1.duckdb")

What are the available tables?

SHOW TABLES

Describing a table

DESCRIBE person

Data Types

If you look at the column_type for one of the DESCRIBE statements above, you’ll notice there are different data types:

• INTEGER
• TIMESTAMP
• DATE
• VARCHAR

You can see all of the datatypes that are available in DuckDB here.

SELECT and FROM

SELECT is a clause that lets you pick out columns of interest. If you want all columns, use *.

FROM is a clause that lets you decide which table to work with.

SELECT * 
  FROM person 
  LIMIT 10;

SELECT for specific columns

Instead of * for all columns, we can specify the columns of interest:

SELECT person_id, birth_datetime, gender_concept_id 
  FROM person
  LIMIT 10;

WHERE - filtering our table

Adding WHERE to our SQL statement lets us add filtering to our query:

SELECT person_id, gender_source_value, race_source_value, year_of_birth 
  FROM person 
  WHERE year_of_birth < 2000

SELECT person_id, gender_source_value, race_source_value 
  FROM person 
  WHERE year_of_birth < 2000

Single quotes and WHERE

Single quotes (‘M’) refer to values, and double quotes refer to columns (“person_id”).

This will trip you up several times if you’re not used to it.

SELECT person_id, gender_source_value
  FROM person 
  WHERE gender_source_value = 'M'
  LIMIT 10;

COUNT - how many entries?

Sometimes you want to know the size of your result, not necessarily return the entire set of results. That is what COUNT is for.

SELECT COUNT(*)
  FROM procedure_occurrence;

SELECT COUNT(procedure_concept_id)
  FROM procedure_occurrence;

COUNT DISTINCT for unique entries

When you have repeated values, COUNT(DISTINCT ) can help you find the number of unique values in a column:

SELECT COUNT(DISTINCT procedure_concept_id)
  FROM procedure_occurrence

SELECT DISTINCT procedure_concept_id
  FROM procedure_occurrence;

Revisiting DESCRIBE

One of the important properties of data in a relational database is that there are no repeat rows in the database. Each table that meets this restriction has what is called a primary key.

DESCRIBE person

Always close the connection

When we’re done, it’s best to close the connection with dbDisconnect().

dbDisconnect(con)